********************************************************************************
********************************************************************************
* This do file replicates all Online Appendix figures and tables of the 
* employer surveys: Figure A1, Table A1, and Figure E1, E2, and E3
********************************************************************************
********************************************************************************

*******************************************************************************
* Figure A1. The importance of good knowledge of Swedish at the workplace.
*******************************************************************************

use "Data/data_lilla_sprakenkat_employer_level_JHR", clear
keep Lang_important
gen freq_1 = 1
collapse (count)freq_1, by(Lang_important)
save Data/tmp_lilla, replace

use "Data/data_stora_sprakenkaten_JHR", clear
keep B4
keep B4
rename B4 Lang_important
gen freq_2 = 1
collapse (count)freq_2, by(Lang_important)
merge 1:1 Lang_important using Data/tmp_lilla
erase Data/tmp_lilla.dta
drop _merge

graph bar freq_1 freq_2, over(Lang_important) graphregion(color(gs16)) ///
ytitle("Frequency") ///
ylabel(,nogrid) ///
bar(1, color(white))  bar(2, color(black)) ///
bar(1, lcolor(gs0))   bar(2, lcolor(gs0)) ///
legend(symxsize(10) cols(3) label(1 "Survey 1") label(2 "Survey 2")  region(lcolor(white))) graphregion(color(white))

*******************************************************************************
* Table A1. Survey response rate by occupation.
*******************************************************************************

use "Data/data_lilla_sprakenkat_employer_level_attrition_JHR", clear

tab occ, miss

replace occ = "Preschool teachers" if occ == "barnskötare"
replace occ = "Shop sales assistants" if occ == "butikssäljare"
replace occ = "Computer professionals" if occ == "dataspecialist"
replace occ = "Accounting clerks" if occ == "ekonomiassistent"
replace occ = "Truck drivers" if occ == "fordonsförare"
replace occ = "Business salespersons" if occ == "företagssäljare"
replace occ = "Chefs" if occ == "kock"
replace occ = "Office assistants" if occ == "kontorsassistent"
replace occ = "Customer service workers" if occ == "kundtjänstpersonal"
replace occ = "Warehouse staff workers" if occ == "lagerpersonal"
replace occ = "Cleaners" if occ == "lokalvårdare"
replace occ = "Mechanics" if occ == "mekaniker"
replace occ = "HR assistants" if occ == "personaladministratör"
replace occ = "Personal assistants" if occ == "personligassistent"
replace occ = "Accountants" if occ == "redovisningsekonom"
replace occ = "Waiters/waitresses" if occ == "restaurangbiträde"
replace occ = "IT support technicians" if occ == "supporttekniker"

bys occ: tab answered_survey
tab answered_survey if low_skill == 1 
tab answered_survey if medium_skill == 1 
tab answered_survey if high_skill == 1 

*******************************************************************************
* Figure E1. Language proficiency ratings of cover letters, by occupation.
*******************************************************************************

use "Data/data_lilla_sprakenkat_employer_level_JHR", clear
keep rating_bad_CV rating_good_CV occ occ_no

replace  occ = "Preschool teachers" if occ == "barnskötare"
replace  occ = "Shop sales assistants" if occ == "butikssäljare"
replace  occ = "Computer professionals" if occ == "dataspecialist"
replace  occ = "Accounting clerks" if occ == "ekonomiassistent"
replace  occ = "Drivers" if occ == "fordonsförare"
replace  occ = "Business sales persons" if occ == "företagssäljare"
replace  occ = "Chefs" if occ == "kock"
replace  occ = "Office assistants" if occ == "kontorsassistent"
replace  occ = "Customer service workers" if occ == "kundtjänstpersonal"
replace  occ = "Warehouse staff workers" if occ == "lagerpersonal"
replace  occ = "Cleaners" if occ == "lokalvårdare"
replace  occ = "Mechanics" if occ == "mekaniker"
replace  occ = "HR assistants" if occ == "personaladministratör"
replace  occ = "Personal assistants" if occ == "personligassistent"
replace  occ = "Accountants" if occ == "redovisningsekonom"
replace  occ = "Waiters/waitresses" if occ == "restaurangbiträde"
replace  occ = "IT support technicians" if occ == "supporttekniker"

graph bar rating_bad_CV rating_good_CV, over(occ,  label(angle(90)) sort(rating_bad_CV)) ///
ytitle("Rating") ///
ylabel(,nogrid) graphregion(color(gs16)) ///
legend(symxsize(10) cols(1) label(1 "Language level 1") label(2 "Language level 3")) ///
bar(1, fcolor(gs10) lcolor(black)) bar(2, fcolor(black) lcolor(black))


*******************************************************************************
* Figure E2. Employability ratings of cover letters.
*******************************************************************************

use "Data/data_stora_sprakenkaten_JHR", clear
foreach var of varlist * {
 local contains_X3 = strpos("`var'", "03") | strpos("`var'", "13") | strpos("`var'", "23") | strpos("`var'", "33")
 if `contains_X3' > 0 {
  drop `var'
 }
}

foreach var of varlist * {
 preserve
  keep `var'
  gen language_level = substr("`var'", 4, 1) 
  destring language_level, replace
  gen rating_swedish = `var' if substr("`var'", 5, 1) == "1" 
  drop `var'
  gen recr_id = _n
  save Data/tmp_rating_swedish_`var', replace
 restore
}

foreach var of varlist * {
 preserve
  keep `var'
  gen language_level = substr("`var'", 4, 1) 
  destring language_level, replace
  gen probability_callback = `var' if substr("`var'", 5, 1) == "2" 
  drop `var'
  gen recr_id = _n
  save Data/tmp_probability_callback_`var', replace
 restore
}

local first_time = 1 
foreach var of varlist * {
 if `first_time' == 1 {
  clear
  local first_time = 0
 }
 append using Data/tmp_rating_swedish_`var', force
 erase Data/tmp_rating_swedish_`var'.dta
}

drop if rating_swedish == .
sort recr_id language_level
replace language_level = (-1) * language_level + 4

use "Data/data_stora_sprakenkaten_JHR", clear
foreach var of varlist * {
 local contains_X3 = strpos("`var'", "03") | strpos("`var'", "13") | strpos("`var'", "23") | strpos("`var'", "33")
 if `contains_X3' > 0 {
  drop `var'
 }
}

local first_time = 1 
foreach var of varlist * {
 if `first_time' == 1 {
  clear
  local first_time = 0
 }
 append using Data/tmp_probability_callback_`var', force
 erase Data/tmp_probability_callback_`var'.dta
}

drop if probability_callback == .
sort recr_id language_level
replace language_level = (-1) * language_level + 4
save Data/tmp_probability_callback, replace

use "Data/data_lilla_sprakenkat_employer_level_JHR", clear
keep rating_good_CV  occ
rename rating_good_CV rating_swedish_Survey_B
gen language_level = 3
save Data/tmp_rating_good_CV, replace
use "Data/data_lilla_sprakenkat_employer_level_JHR", clear
keep rating_bad_CV  occ
rename rating_bad_CV rating_swedish_Survey_B
gen language_level = 1
append using Data/tmp_rating_good_CV, force
erase Data/tmp_rating_good_CV.dta

use "Data/data_lilla_sprakenkat_employer_level_JHR", clear
keep interview_good_CV  occ
rename interview_good_CV interview_swedish_Survey_B
gen language_level = 3
save Data/tmp_interview_good_CV, replace
use "Data/data_lilla_sprakenkat_employer_level_JHR", clear
keep interview_bad_CV  occ
rename interview_bad_CV interview_swedish_Survey_B
gen language_level = 1
append using Data/tmp_interview_good_CV, force
erase Data/tmp_interview_good_CV.dta

append using Data/tmp_probability_callback, force
erase Data/tmp_probability_callback.dta
rename probability_callback interview_swedish
collapse interview_swedish interview_swedish_Survey_B, by(language_level)

twoway (scatter interview_swedish_Survey_B language_level, ///
mfcolor(white) mlcolor(black) msymbol(triangle) ///
legend(symxsize(10) cols(1) label(1 "Survey 1") label(2 "Survey 2"))) ///
(connected interview_swedish language_level, ///
ytitle("Rating") ///
xtitle("Language level") ///
ylabel(,nogrid) graphregion(color(gs16)) ///
xlabel(,nogrid) ///
mcolor(black) lcolor(black) ylabel(0(1)7) yscale(range(0(1)7))) 


*******************************************************************************
* Figure E3. Employability ratings of cover letters, by occupation.
*******************************************************************************

use "Data/data_lilla_sprakenkat_employer_level_JHR", clear
keep interview_bad_CV interview_good_CV occ occ_no

replace  occ = "Preschool teachers" if occ == "barnskötare"
replace  occ = "Shop sales assistants" if occ == "butikssäljare"
replace  occ = "Computer professionals" if occ == "dataspecialist"
replace  occ = "Accounting clerks" if occ == "ekonomiassistent"
replace  occ = "Drivers" if occ == "fordonsförare"
replace  occ = "Business sales persons" if occ == "företagssäljare"
replace  occ = "Chefs" if occ == "kock"
replace  occ = "Office assistants" if occ == "kontorsassistent"
replace  occ = "Customer service workers" if occ == "kundtjänstpersonal"
replace  occ = "Warehouse staff workers" if occ == "lagerpersonal"
replace  occ = "Cleaners" if occ == "lokalvårdare"
replace  occ = "Mechanics" if occ == "mekaniker"
replace  occ = "HR assistants" if occ == "personaladministratör"
replace  occ = "Personal assistants" if occ == "personligassistent"
replace  occ = "Accountants" if occ == "redovisningsekonom"
replace  occ = "Waiters/waitresses" if occ == "restaurangbiträde"
replace  occ = "IT support technicians" if occ == "supporttekniker"

graph bar interview_bad_CV interview_good_CV, over(occ,  label(angle(90)) sort(interview_bad_CV)) ///
ytitle("Rating") ///
ylabel(,nogrid) graphregion(color(gs16)) ///
legend(symxsize(10) cols(1) label(1 "Language level 1") label(2 "Language level 3")) ///
bar(1, fcolor(gs10) lcolor(black)) bar(2, fcolor(black) lcolor(black))



